In [114]:
import pandas as pd
import numpy as np
import sqlite3
pd.set_option('display.max_columns', None)
import matplotlib as plt
import plotly.express as px
from scipy import stats
import sqlite3
- Store Analysis:
- Categorize the retail liquor stores into different types, chain-super market, specialized liquor stores, convenience stores, news stands, tobacco stores,..... and report the relevant statistics.
- New stores pop up and some of them shut down after a few years. Analyze the store opening dynamics
and report the findings on store life-cycle, store survival analysis, survial curve, survival
probabilities, etc. Refine your findings based on the store categories you design. Is it easy for
the stores to experience sales volume growth? With new stores popping up for competition, how does
it affect the sales of the existing stores?
Does the pie (Gross sales volume in Iowa) grow bigger? Do the stores share the bigger pie? Does the winner take all or more players share smaller slices? - In terms of store inventories, report the varieties of product/product category for different store types. What product/product category are the major sales contributors for different types of stores?
- Study the monthly sales volumes and gross profits (without taking price-discounts into account) of the stores.
- Use the county population information, say https://www.iowa-demographics.com/counties_by_population, to estimate the stores-per-capita in different counties. Use the chain-supermarket Hy-Vee as a case study, depict its store distribution in different counties of Iowa and the their liquor sales dynamics.
Store Category statistics¶
- describe for each category
- histogram
- stores look at store that has the most sales volume, sales, (in which county)
- histogram of sales volume monthly or quarterly
- histogram
Query Data from db¶
In [229]:
# query data frame with store and transaction info
conn = sqlite3.connect("data/liquor.db")
query = '''
SELECT Date, t."Store Number", Sale, "Volume Sold", County, "Store Type"
FROM transactions AS t
LEFT JOIN stores AS s ON t."Store Number" = s."Store Number";
'''
store_df = pd.read_sql_query(query, conn)
conn.close()
store_df.head(3)
Out[229]:
In [230]:
# Make Date column datetime
store_df.loc[:,'Date']=pd.to_datetime(store_df.Date)
In [231]:
store_df.head()
Out[231]:
In [232]:
# Combined the Categories to Other for final presentation
store_df['Store Type'] = store_df['Store Type'].replace(['Other', 'Other Grocery or Convenience', 'Casino'], 'T Other')
In [263]:
store_df.head()
Out[263]:
CSV for R¶
- to create combined bar and line graph for volume sale growth overall and by store type
In [296]:
import datetime
# Create df grouped by year summing volume sales
store_line_vol_y= store_df.groupby([pd.Grouper(key='Date', freq='Y')]).agg({'Volume Sold':'sum'}).reset_index()
# Create column Year
store_line_vol_y['Year'] = pd.DatetimeIndex(store_line_vol_y['Date']).year
# Convert Volume column to millions of dollars
store_line_vol_y['Volume Sold'] = store_line_vol_y['Volume Sold']/1000000
In [297]:
# Create df group by Y and Store type sum volume sold
store_bar_p_y = store_df.groupby([pd.Grouper(key='Date', freq='Y'),'Store Type']).agg({'Volume Sold':'sum'}).reset_index()
# Create column Percent for normalized store volumes sold
store_bar_p_y['Percent'] = store_bar_p_y.groupby([pd.Grouper(key='Date', freq='Y')]).transform(lambda x: (x/sum(x))*17.5)
# Create year column
store_bar_p_y['Year'] = pd.DatetimeIndex(store_bar_p_y['Date']).year
In [ ]:
# Merge dataframes
store_r = pd.merge(store_bar_p_y,store_line_vol_y,how='left', on= 'Year')
# Get rid of 2020 data
store_r = store_r.loc[store_r.Year!=2020]
In [300]:
store_r['Store Type'] = store_r['Store Type'].replace(['T Other'], 'Other')
In [301]:
# and write to csv for R
store_r.to_csv('data/store_r2.csv',index=False)
EDA¶
Overall Sales and Volumn Sold¶
In [5]:
store_df.describe()
Out[5]:
Sale and Volume by Store Type¶
In [264]:
store_type_g = store_df.groupby('Store Type')
store_type_g.describe()
Out[264]:
In [265]:
# Create grouped object that groups by Month
store_m_g = store_df.groupby([pd.Grouper(key='Date',freq='M')])
In [266]:
# histogram of overall monthly sales volume
fig = px.histogram(store_m_g.sum().reset_index(),
x='Volume Sold', nbins=25, title='Overall Monthly sales Volume')
fig
In [267]:
# Create grouped object that groups by store type and Month
store_type_m_g = store_df.groupby(['Store Type',
pd.Grouper(key='Date',
freq='M')])
In [81]:
# histogram of store monthly sales volume divided by store type
fig = px.histogram(store_type_m_g.sum().reset_index(),
x='Volume Sold',
facet_col='Store Type',
facet_col_wrap=2)
fig
In [82]:
# look at the quarterly sales per store and get histogram
store_nu_q_g = store_df.groupby(['Store Number', pd.Grouper(key='Date',freq='Q')])
In [83]:
fig = px.histogram(store_nu_q_g.sum().reset_index(),
x='Volume Sold', nbins=30, title='Monthly Volume Sold per Store')
fig
Store Type that Buy/Sells most Volume¶
In [268]:
# Volume Sold by Store type
# Supermarkets sell the most liquor
store_type_g['Volume Sold'].sum().sort_values(ascending=False).plot.bar()
Out[268]:
In [269]:
# boxplots showing montly sales volume category may be better way to represent volume sales by store type
fig = px.box(store_type_m_g.sum().sort_values(by='Volume Sold',ascending=False).reset_index(),
x = 'Store Type',
y ='Volume Sold',
title='Total Monthly Sales Volume by Store Type')
fig
In [270]:
store_m_ty_no_g_ave=store_df.groupby(['Store Type', pd.Grouper(key='Date', freq='M'), 'Store Number']).sum().reset_index().groupby(['Store Type', pd.Grouper(key='Date', freq='M')]).mean().reset_index()
In [271]:
store_ave_map = {'Supermarket':1, 'Casino':6, 'Other':4, 'Drug Store':3, 'Liquor Tobacco Store':2, 'Other Grocery or Convenience':5, 'Convenience Store':7}
store_m_ty_no_g_ave['Order'] = store_m_ty_no_g_ave['Store Type'].map(store_ave_map)
In [272]:
fig = px.box(store_m_ty_no_g_ave.sort_values(by='Order'),
x = 'Store Type',
y ='Volume Sold',
title='Average Monthly Store Sales Volume')
fig.update_layout(yaxis_title="Volume Sold (Liters)",
title={
'y':0.9,
'x':0.5,
'xanchor': 'center',
'yanchor': 'top'})
In [273]:
store_df.head()
Out[273]:
In [ ]:
In [ ]:
In [274]:
tt_store_mean= store_type_m_g.mean().reset_index()
In [275]:
store_ave_map = {'Supermarket':1, 'Casino':2, 'Other':3, 'Drug Store':4, 'Liquor Tobacco Store':5, 'Other Grocery or Convenience':6, 'Convenience Store':7}
tt_store_mean["Order"] = tt_store_mean["Store Type"].map(store_ave_map)
In [276]:
# Supermarket, Casino, Drug Store, Liquor, and Convenience Store
# are significantly different from each other
px.box(tt_store_mean.sort_values(by='Order').reset_index(),
x = 'Store Type',
y ='Volume Sold',
title='Average Monthly Store Sales Volume by Store Type')
In [130]:
from scipy import stats
# Casino and Supermarket are significantly different
# pvalue=0.02681811922044221
stats.ttest_ind(tt_store_mean.loc[tt_store_mean['Store Type']=='Casino']['Volume Sold'],tt_store_mean.loc[tt_store_mean['Store Type']=='Supermarket']['Volume Sold'])
Out[130]:
In [140]:
# Casino and Other are not significantly different
# pvalue=0.50463932688488054
stats.ttest_ind(tt_store_mean.loc[tt_store_mean['Store Type']=='Casino']['Volume Sold'],tt_store_mean.loc[tt_store_mean['Store Type']=='Other']['Volume Sold'])
Out[140]:
In [145]:
# Casino and Drug Store are significantly different
# pvalue=7.41395120593647e-17
stats.ttest_ind(tt_store_mean.loc[tt_store_mean['Store Type']=='Casino']['Volume Sold'],tt_store_mean.loc[tt_store_mean['Store Type']=='Drug Store']['Volume Sold'])
Out[145]:
In [146]:
# Liquor and Drug Store are significantly different
# pvalue=3.888875737051862e-15
stats.ttest_ind(tt_store_mean.loc[tt_store_mean['Store Type']=='Drug Store']['Volume Sold'],tt_store_mean.loc[tt_store_mean['Store Type']=='Liquor Tobacco Store']['Volume Sold'])
Out[146]:
In [129]:
tt_store_mean.loc[tt_store_mean['Store Type']=='Casino']['Volume Sold']
Out[129]:
In [128]:
tt_store_mean
Out[128]:
Seasonality by store type based off of sales volume¶
In [277]:
# GROUP BY YEAR AND SEE TREND
In [278]:
px.line(store_df.groupby(['Store Type', pd.Grouper(key='Date', freq='Y')])[['Volume Sold']].sum().reset_index(),x='Date', y='Volume Sold', color = 'Store Type', title='Volume Sold by Month and Store Type')
In [279]:
fig = px.line(store_df.groupby(['Store Type', pd.Grouper(key='Date', freq='Q')])[['Volume Sold']].sum().reset_index(),x='Date', y='Volume Sold', color = 'Store Type',title='Total Quarterly Sales Volume')
fig.update_layout(yaxis_title="Volume Sold (Liters)",
title={
'y':0.9,
'x':0.5,
'xanchor': 'center',
'yanchor': 'top'})
In [280]:
# Use groupby defined earlier
store_vol = store_type_m_g[['Volume Sold']].sum().reset_index()
# Pivot to get line graph
store_vol = store_vol.pivot(index = 'Date',
columns = 'Store Type',
values = 'Volume Sold').reset_index()
# Figure showing volume sold by store category over the months
# Takeaway: Seasonality shows up in all categories except Casino(too small).
# Largest fluctuations seem to result from Grocery stores then Liquor and then Drug stores.
# Convenience store sales might be on the rise
fig = px.line(store_vol, x='Date', y=store_vol.columns, title='Volume Sold by Month and Store Type')
fig
Overall Volume Sales by month¶
In [92]:
fig = px.line(store_m_g.sum().reset_index(),
x='Date',
y='Volume Sold',
title='Total Volume Sold by Month')
fig
# Overall upward trend
Store Life Cycle¶
New stores pop up and some of them shut down after a few years. Analyze the store opening dynamics and report the findings on store life-cycle, store survival analysis, survial curve, survival probabilities, etc. Refine your findings based on the store categories you design. Is it easy for the stores to experience sales volume growth? With new stores popping up for competition, how does it affect the sales of the existing stores? Does the pie (Gross sales volume in Iowa) grow bigger? Do the stores share the bigger pie? Does the winner take all or more players share smaller slices
Overall Longevity¶
In [93]:
store_lon = store_df.groupby(['Store Number']).agg({'Date':
(lambda x: (max(x)-min(x)).days)}).sort_values(by='Date',
ascending=False).reset_index()
In [94]:
store_lon.describe()
Out[94]:
In [95]:
fig = px.histogram(store_lon['Date'], nbins=30)
fig.update_layout(
title='Store Longevity',
xaxis_title="Number of Days Open",
legend_title="",
)
fig
Store-type Longevity¶
In [96]:
store_t_lon = store_df.groupby(['Store Type',
'Store Number']).agg({'Date':
(lambda x:
(max(x)-min(x)).days)}).sort_values(by='Date',
ascending=False).reset_index()
store_t_lon.head(3)
Out[96]:
In [97]:
fig = px.box(store_t_lon,
x = 'Store Type',
y ='Date',
title='Longevity by Store Type')
fig
# doesnt take into consideration stores that opened more recently
Number of Stores over years¶
In [98]:
store_df.head()
# count number of unique stores each time period (m)
Out[98]:
In [99]:
# overlay number of stores closed as well (no purchase for 6 months)
# think about the ratio of closure/open
In [100]:
fig = px.line(store_m_g.agg({'Store Number': pd.Series.nunique}).reset_index(),
x='Date', y='Store Number', title='Store Growth')
fig
In [281]:
# For some stores with long longevity how sales change over time
In [282]:
store_type_nu = store_type_m_g.agg({'Store Number': pd.Series.nunique}).reset_index().pivot(index= 'Date', columns = 'Store Type', values = 'Store Number').reset_index()
fig = px.line(store_type_nu, x='Date', y=store_type_nu.columns, title='Number of Stores by Store Type')
fig
# Number of convenience stores is growing rapidly
In [293]:
fig = px.line(store_type_q_g.agg({'Store Number': pd.Series.nunique}).reset_index(), x='Date', y='Store Number',color= 'Store Type', title='Store Number Growth')
fig.update_layout(yaxis_title="Number of Stores",
title={
'y':0.9,
'x':0.5,
'xanchor': 'center',
'yanchor': 'top'})
In [284]:
store_type_q_g
Out[284]:
In [254]:
store_type_q_g.sum()
Out[254]:
Divide volume by number stores in cat to get average? Vol sold by store. Over time is each store selling more or less¶
In [285]:
store_type_m_g_vol = store_type_m_g.agg({'Volume Sold': 'sum','Store Number': pd.Series.nunique}).reset_index()
In [286]:
store_type_m_g_vol['AveStoreSale'] = store_type_m_g_vol['Volume Sold']/store_type_m_g_vol['Store Number']
In [287]:
px.line(store_type_m_g_vol, x='Date', y='AveStoreSale', color='Store Type', title='Average Volume Sold per Store by Store Type')
PLEASE USE G1¶
In [288]:
store_type_q_g = store_df.groupby(['Store Type', pd.Grouper(key='Date', freq='Q')])
In [289]:
store_type_q_g_vol = store_type_q_g.agg({'Volume Sold': 'sum','Store Number': pd.Series.nunique}).reset_index()
In [290]:
store_type_q_g_vol['AveStoreSale'] = store_type_q_g_vol['Volume Sold']/store_type_q_g_vol['Store Number']
In [295]:
fig =px.line(store_type_q_g_vol, x='Date', y='AveStoreSale', color='Store Type',title='Per Store Average Quarterly Sales Volume')
fig.update_layout(yaxis_title="Volume Sold (Liters)",
title={
'y':0.9,
'x':0.5,
'xanchor': 'center',
'yanchor': 'top'})
Some exploration of Vodka¶
when have Xiaogang data - do some exploration of diff between two for price of vodka
top 20 products and stacked barchart of types?
In [11]:
conn = sqlite3.connect("data/liquor.db")
query = '''
SELECT *
FROM transactions AS t
LEFT JOIN products AS p ON t."Item Number" = p."Item Number"
LIMIT(1)
'''
df = pd.read_sql_query(query, conn)
conn.close()
df.head(3)
Out[11]:
In [12]:
conn = sqlite3.connect("data/liquor.db")
query = '''
SELECT Date, p.product_name, category_new, "Volume Sold", proof, Sale
FROM transactions AS t
LEFT JOIN products AS p ON t."Item Number" = p."Item Number"
'''
df = pd.read_sql_query(query, conn)
conn.close()
df.head(3)
Out[12]:
In [13]:
df.loc[:,'Date']=pd.to_datetime(df.Date)
In [27]:
prod = df.loc[df.category_new=='vodka'].groupby([pd.Grouper(key='Date', freq='M'),'product_name']).agg({'Volume Sold':'sum', 'Sale':'sum', 'proof':'median'}).reset_index()
In [28]:
prod['PriceLiter'] = prod['Sale']/prod['Volume Sold']
In [29]:
prod.head()
Out[29]:
In [30]:
prod.loc[prod['product_name']=='titos handmade vodka']
Out[30]:
In [33]:
px.line(prod.loc[prod['product_name']=='hawkeye vodka'], x='Date', y= 'PriceLiter' )
In [36]:
px.line(prod.loc[prod['product_name']=='titos handmade vodka'], x='Date', y= 'PriceLiter' )
In [31]:
prod.loc[prod['product_name']=='hawkeye vodka']
Out[31]:
In [57]:
topvod=prod.groupby('product_name').agg({'Volume Sold':'sum'}).sort_values(by='Volume Sold',ascending=False).reset_index().head(20)
In [58]:
topvodlist = topvod.product_name.tolist()
In [59]:
px.line(prod.loc[prod['product_name'].isin(topvodlist)], x='Date', y='PriceLiter', color = 'product_name')
In [63]:
prod.loc[prod['product_name'].isin(topvodlist)].describe()
Out[63]:
In [95]:
# need to get rid of some zeros in volume sold for all vodkas
prod.loc[prod['Volume Sold']!=0].describe()
Out[95]:
In [96]:
from scipy import stats
# Top 20 PriceLiter is significantly different from rest of vodka
stats.ttest_ind(prod.loc[prod['Volume Sold']!=0]['PriceLiter'],prod.loc[prod['product_name'].isin(topvodlist)]['PriceLiter'])
In [112]:
# Top 20 proof is significantly different from rest of vodka
stats.ttest_ind(prod.loc[~np.isnan(prod.proof)]['proof'],prod.loc[(prod['product_name'].isin(topvodlist))&(~np.isnan(prod.proof))]['proof'])
Out[112]:
In [ ]: